import json

import mysql_util as sql_util

"""SQL生成JAVA代码"""


def snake_to_camel(column_name):
    column_name = column_name.lower()
    for word in column_name.split('_'):
        column_name = column_name.replace('_' + word.lower(), word.capitalize())
    return column_name


def table_2_sql(table_name: str):
    # table = 'report_doc_stat'
    sql = "SELECT column_name, ordinal_position, is_nullable, DATA_TYPE, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE 1 = 1 AND table_name = '{}'".format(
        table)
    list = sql_util.select_by_sql(sql)
    list = json.loads(list)
    print(list)
    sql = 'select '
    for c in list:
        sql = sql + c['column_name'] + ' "' + c['column_comment'] + '",'
    sql = sql[:-1]
    sql = sql + ' from ' + table_name
    print(sql)

    # insert 语句
    sql = 'INSERT INTO ' + table_name + ' ('
    for c in list:
        sql = sql + c['column_name'] + ','
    sql = sql[:-1]
    sql = sql + ') values ('
    for c in list:
        sql = sql + ':' + snake_to_camel(c['column_name']) + ','
    sql = sql[:-1]
    sql = sql + ')'
    print(sql)

    # on duplicate key update
    sql = 'on duplicate key update '
    for c in list:
        sql = sql + c['column_name'] + ' = values(' + c['column_name'] + '),'
    print(sql)


def table_2_class(table_name: str):
    # table_name = 'report_cost_close_fat'.upper()
    sql = "SELECT column_name, ordinal_position, is_nullable, DATA_TYPE, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE 1 = 1 AND table_name = '{}'".format(
        table_name).upper()
    table_name2 = table_name.lower().capitalize()
    for word in table_name2.split('_'):
        table_name2 = table_name2.replace('_' + word.lower(), word.capitalize())
    print(table_name2)
    rs = sql_util.select_by_sql(sql)
    rs = json.loads(rs)
    print(type(rs))
    print(rs)
    print('\n')
    for t in rs:
        comments = t['COLUMN_COMMENT']
        if comments is None:
            comments = ''
        data_type = str(t['DATA_TYPE']).upper()
        # 根据数据字段映射java类型
        field_type = ''
        if 'DATE' in data_type or 'LocalDateTime' in data_type:
            field_type = 'Date'
            print('@JsonFormat(pattern = "yyyy/MM/dd HH:mm:ss")')
        elif 'DECIMAL' in data_type:
            field_type = 'BigDecimal'
        elif 'INT' in data_type:
            field_type = 'Integer'
        elif 'VARCHAR' in data_type:
            field_type = 'String'

        column_name = t['COLUMN_NAME'].lower()
        for word in column_name.split('_'):
            column_name = column_name.replace('_' + word.lower(), word.capitalize())
        # 转驼峰
        field = '@ApiModelProperty(value = "{}")\nprivate {} {};'.format(comments, field_type, column_name)
        print(field)
        print('\n')


if __name__ == '__main__':
    print('begin')
    table = 'ill_immunity_consumable'
    # sql生成查询语句
    table_2_sql(table)

    table_2_class(table)
